﻿using BCCommon;
using BCEntity.TMS.Packing.PackingConfig;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using Dapper;
using System.Data;
using BCEntity.TMS.Packing.PackingConfigOverwrite;

namespace BCData.TMS.Packing.PackingConfig
{
    public class PackingConfigOverwriteData : IPackingConfigOverwriteData
    {
        private readonly IDatabaseContext database;

        private const string QUERY = @"SELECT
										A.PackingConfigOverwriteId,
										A.PackingConfigId,
										A.PackingCategoryConfigId,
										A.PackingTitle,
										A.PackingWidth,
										A.PackingLength,
										A.PackingHeight,
										A.DimensionUnit,
										A.ShowUnit,
										A.TransformRatio,
										A.Price,
										A.Enabled,
										A.ProvinceCode,
										A.CityCode,
										A.CountyCode,
										A.AgentRecordId,
										A.CreateAdminId,
										A.CreateAdminName,
										A.CreateTime,
										A.UpdateTime,
										A.EditAdminId,
										A.EditAdminName 
									FROM
										PackingConfigOverwrite A";

        public PackingConfigOverwriteData(IDatabaseContext database)
        {
            this.database = database;
        }

        public async Task<(IEnumerable<PackingConfigOverwriteViewEntity>, int)> Query(long? agentRecordId, int? packingCategoryConfigId, string packingTitle, 
			string provinceCode, string cityCode, string countyCode, bool? enabled, DateTime? createStartDate, DateTime? createEndDate, int pageIndex, int pageSize)
        {
            #region
            List<string> wheres = new List<string>();
			DynamicParameters parameters = new DynamicParameters();
			if (agentRecordId.HasValue)
			{
				wheres.Add("A.AgentRecordId = ?AgentRecordId");
				parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
			}
			if (packingCategoryConfigId.HasValue)
            {
				wheres.Add("A.PackingCategoryConfigId = ?PackingCategoryConfigId");
				parameters.Add("?PackingCategoryConfigId", packingCategoryConfigId.Value, DbType.Int32);
            }
			if (!string.IsNullOrWhiteSpace(packingTitle))
            {
				wheres.Add("A.PackingTitle LIKE ?PackingTitle");
				parameters.Add("?PackingTitle", $"%{packingTitle}%", DbType.AnsiString);
            }
			if (!string.IsNullOrWhiteSpace(provinceCode))
            {
				wheres.Add("A.ProvinceCode = ?ProvinceCode");
				parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
            }
			if (!string.IsNullOrWhiteSpace(cityCode))
			{
				wheres.Add("A.CityCode = ?CityCode");
				parameters.Add("?CityCode", cityCode, DbType.AnsiString);
			}
			if (!string.IsNullOrWhiteSpace(countyCode))
			{
				wheres.Add("A.CountyCode = ?CountyCode");
				parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
			}
			if (enabled.HasValue)
            {
				wheres.Add("A.Enabled = ?Enabled");
				parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
            }
			if (createStartDate.HasValue)
            {
				wheres.Add("A.CreateTime >= ?CreateStartDate");
				parameters.Add("?CreateStartDate", createStartDate.Value.Date, DbType.Date);
            }
			if (createEndDate.HasValue)
			{
				wheres.Add("A.CreateTime < ?CreateEndDate");
				parameters.Add("?CreateEndDate", createEndDate.Value.AddDays(1).Date, DbType.Date);
			}
            #endregion
            #region
   //         string sql = @"SELECT 
			//				D.AgentRecordId,D.ProvinceCode,E.ProvinceName,D.CityCode,E.CityName,D.CountyCode,E.CountyName,
			//				D.IsChildAgent,D.IsEpibole,D.PackingConfigId,D.PackingCategoryConfigId,
			//				IFNULL(F.PackingTitle,D.PackingTitle) AS PackingTitle,
			//				IFNULL(F.PackingWidth,D.PackingWidth) AS PackingWidth,
			//				IFNULL(F.PackingLength,D.PackingLength) AS PackingLength,
			//				IFNULL(F.PackingHeight,D.PackingHeight) AS PackingHeight,
			//				IFNULL(F.DimensionUnit,D.DimensionUnit) AS DimensionUnit,
			//				IFNULL(F.ShowUnit,D.ShowUnit) AS ShowUnit,
			//				IFNULL(F.TransformRatio,D.TransformRatio) AS TransformRatio,
			//				IFNULL(F.Price,D.Price) AS Price,
			//				IFNULL(F.CreateAdminId,D.CreateAdminId) AS CreateAdminId,
			//				IFNULL(F.CreateAdminName,D.CreateAdminName) AS CreateAdminName,
			//				IFNULL(F.CreateTime,D.CreateTime) AS CreateTime,
			//				IFNULL(F.UpdateTime,D.UpdateTime) AS UpdateTime,
			//				IFNULL(F.EditAdminId,D.EditAdminId) AS EditAdminId,
			//				IFNULL(F.EditAdminName,D.EditAdminName) AS EditAdminName,
			//				D.PackingCategory,
			//				CASE WHEN F.Enabled IS NULL THEN D.Enabled ELSE F.Enabled END AS Enabled,
			//				F.PackingConfigOverwriteId 
			//			FROM
			//			(
			//				SELECT A.AgentRecordId,A.ProvinceCode,A.CityCode,A.CountyCode,A.IsChildAgent,A.IsEpibole,
			//						B.PackingConfigId,B.PackingCategoryConfigId,B.PackingTitle,B.PackingWidth,B.PackingLength,B.PackingHeight,
			//						B.DimensionUnit,B.ShowUnit,B.TransformRatio,B.Price,B.Enabled,
			//						B.CreateAdminId,B.CreateAdminName,B.CreateTime,B.UpdateTime,B.EditAdminId,B.EditAdminName,
			//						C.PackingCategory
			//				FROM AgentAreaRecord A,PackingConfig B
			//				INNER JOIN PackingCategoryConfig C ON B.PackingCategoryConfigId = C.PackingCategoryConfigId
			//				WHERE A.AgentRecordId = ?AgentRecordId
			//			) D
			//			INNER JOIN County E ON D.ProvinceCode = E.ProvinceCode AND D.CityCode = E.CityCode AND D.CountyCode = E.CountyCode
			//			LEFT JOIN PackingConfigOverwrite F ON F.PackingConfigId = D.PackingConfigId AND D.AgentRecordId = F.AgentRecordId 
			//			AND F.PackingCategoryConfigId = D.PackingCategoryConfigId 
			//			AND F.ProvinceCode = D.ProvinceCode
			//			AND F.CityCode = D.CityCode
			//			AND F.CountyCode = D.CountyCode";
			//string countSql = @"SELECT COUNT(1) 
			//			FROM
			//			(
			//				SELECT A.AgentRecordId,A.ProvinceCode,A.CityCode,A.CountyCode,A.IsChildAgent,A.IsEpibole,
			//						B.PackingConfigId,B.PackingCategoryConfigId,B.PackingTitle,B.PackingWidth,B.PackingLength,B.PackingHeight,
			//						B.DimensionUnit,B.ShowUnit,B.TransformRatio,B.Price,B.Enabled,
			//						B.CreateAdminId,B.CreateAdminName,B.CreateTime,B.UpdateTime,B.EditAdminId,B.EditAdminName,
			//						C.PackingCategory
			//				FROM AgentAreaRecord A,PackingConfig B
			//				INNER JOIN PackingCategoryConfig C ON B.PackingCategoryConfigId = C.PackingCategoryConfigId
			//				WHERE A.AgentRecordId = ?AgentRecordId
			//			) D
			//			INNER JOIN County E ON D.ProvinceCode = E.ProvinceCode AND D.CityCode = E.CityCode AND D.CountyCode = E.CountyCode
			//			LEFT JOIN PackingConfigOverwrite F ON F.PackingConfigId = D.PackingConfigId AND D.AgentRecordId = F.AgentRecordId 
			//			AND F.PackingCategoryConfigId = D.PackingCategoryConfigId 
			//			AND F.ProvinceCode = D.ProvinceCode
			//			AND F.CityCode = D.CityCode
			//			AND F.CountyCode = D.CountyCode";

			string sql = @"SELECT 
							A.PackingConfigOverwriteId,
							A.PackingConfigId,
							A.PackingCategoryConfigId,
							A.PackingTitle,
							A.PackingWidth,
							A.PackingLength,
							A.PackingHeight,
							A.DimensionUnit,
							A.ShowUnit,
							A.TransformRatio,
							A.Price,
							A.Enabled,
							A.ProvinceCode,
							A.CityCode,
							A.CountyCode,
							A.AgentRecordId,
							A.CreateAdminId,
							A.CreateAdminName,
							A.CreateTime,
							A.UpdateTime,
							A.EditAdminId,
							A.EditAdminName,
							B.ProvinceName,B.CityName,B.CountyName,
							C.PackingCategory
						FROM  PackingConfigOverwrite A 
						INNER JOIN County B ON A.ProvinceCode = B.ProvinceCode AND A.CityCode = B.CityCode AND A.CountyCode = B.CountyCode
						INNER JOIN PackingCategoryConfig C ON A.PackingCategoryConfigId = C.PackingCategoryConfigId";
			string countSql = @"SELECT COUNT(1) 
						FROM  PackingConfigOverwrite A ";
			#endregion
			sql = string.Format("{0} {1} ORDER BY A.PackingConfigId,A.PackingCategoryConfigId DESC LIMIT {2},{3};{4} {1}",
								sql,
								wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty,
								(pageIndex - 1) * pageSize,
								pageSize,
								countSql);
			return await database.Slave.QueryPageableListAsync<PackingConfigOverwriteViewEntity>(sql, parameters);
        }

        public async Task<PackingConfigOverwriteEntity> GetEntityByCategoryConfigId(int packingCategoryConfigId)
        {
			return await database.Slave.QueryEntityAsync<PackingConfigOverwriteEntity>($"{QUERY} WHERE A.PackingCategoryConfigId = ?PackingCategoryConfigId LIMIT 1", new { packingCategoryConfigId });
        }

        public async Task<PackingConfigOverwriteEntity> GetEntity(long packingConfigOverwriteId)
        {
			return await database.Slave.QueryEntityAsync<PackingConfigOverwriteEntity>($"{QUERY} WHERE A.PackingConfigOverwriteId = ?PackingConfigOverwriteId", new { packingConfigOverwriteId });
        }

        public async Task<PackingConfigOverwriteEntity> GetEntity(long agentRecordId, long packingConfigId, string provinceCode, string cityCode, string countyCode)
        {
			return await database.Slave.QueryEntityAsync<PackingConfigOverwriteEntity>($@"{QUERY} WHERE A.AgentRecordId = ?AgentRecordId AND A.PackingConfigId = ?PackingConfigId 
				AND A.ProvinceCode = ?ProvinceCode AND A.CityCode = ?CityCode AND A.CountyCode = ?CountyCode LIMIT 1",
				new { agentRecordId, packingConfigId, provinceCode, cityCode, countyCode });
        }

        public async Task<PackingConfigOverwriteEntity> Insert(PackingConfigOverwriteEntity entity)
        {
			return await database.Master.InsertAsync(entity);
        }

        public async Task<PackingConfigOverwriteEntity> Update(PackingConfigOverwriteEntity entity)
        {
			return await database.Master.UpdateAsync(entity);
        }

        public async Task<int> Delete(int packingConfigOverwriteId)
        {
            return await database.Master.ExecuteAsync("DELETE FROM PackingConfigOverwrite WHERE PackingConfigOverwriteId = ?PackingConfigOverwriteId", new { packingConfigOverwriteId });
		}

        public async Task<IEnumerable<PackingConfigOverwriteViewEntity>> GetList(long? agentRecordId, int? packingCategoryConfigId, string packingTitle, bool? enabled, string provinceCode, string cityCode, string countyCode)
        {
			List<string> wheres = new List<string>();
			DynamicParameters parameters = new DynamicParameters();
			if (agentRecordId.HasValue)
            {
				wheres.Add("A.AgentRecordId = ?AgentRecordId");
				parameters.Add("?AgentRecordId", agentRecordId, DbType.Int64);
			}
			if (packingCategoryConfigId.HasValue)
			{
				wheres.Add("A.PackingCategoryConfigId = ?PackingCategoryConfigId");
				parameters.Add("?PackingCategoryConfigId", packingCategoryConfigId.Value, DbType.Int32);
			}
			if (!string.IsNullOrWhiteSpace(packingTitle))
			{
				wheres.Add("A.PackingTitle LIKE ?PackingTitle");
				parameters.Add("?PackingTitle", $"%{packingTitle}%", DbType.AnsiString);
			}
			if (!string.IsNullOrWhiteSpace(provinceCode))
			{
				wheres.Add("A.ProvinceCode = ?ProvinceCode");
				parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
			}
			if (!string.IsNullOrWhiteSpace(cityCode))
			{
				wheres.Add("A.CityCode = ?CityCode");
				parameters.Add("?CityCode", cityCode, DbType.AnsiString);
			}
			if (!string.IsNullOrWhiteSpace(countyCode))
			{
				wheres.Add("A.CountyCode = ?CountyCode");
				parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
			}
			if (enabled.HasValue)
			{
				wheres.Add("A.Enabled = ?Enabled");
				parameters.Add("?Enabled", enabled.Value, DbType.Boolean);
			}
			string sql = @"SELECT 
							A.PackingConfigOverwriteId,
							A.PackingConfigId,
							A.PackingCategoryConfigId,
							A.PackingTitle,
							A.PackingWidth,
							A.PackingLength,
							A.PackingHeight,
							A.DimensionUnit,
							A.ShowUnit,
							A.TransformRatio,
							A.Price,
							A.Enabled,
							A.ProvinceCode,
							A.CityCode,
							A.CountyCode,
							A.AgentRecordId,
							A.CreateAdminId,
							A.CreateAdminName,
							A.CreateTime,
							A.UpdateTime,
							A.EditAdminId,
							A.EditAdminName,
							B.ProvinceName,B.CityName,B.CountyName,
							C.PackingCategory
						FROM  PackingConfigOverwrite A 
						INNER JOIN County B ON A.ProvinceCode = B.ProvinceCode AND A.CityCode = B.CityCode AND A.CountyCode = B.CountyCode
						INNER JOIN PackingCategoryConfig C ON A.PackingCategoryConfigId = C.PackingCategoryConfigId";
			sql = string.Format("{0} {1} ORDER BY A.PackingConfigId,A.PackingCategoryConfigId DESC", sql, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
			return await database.Slave.QueryAsync<PackingConfigOverwriteViewEntity>(sql, parameters);
		}

        public async Task<IEnumerable<PackingConfigViewEntity>> GetList(int? packingCategoryConfigId, string provinceCode, string cityCode, string countyCode)
        {
			List<string> wheres = new List<string>();
			DynamicParameters parameters = new DynamicParameters();
			if (packingCategoryConfigId.HasValue)
            {
				wheres.Add("A.PackingCategoryConfigId = ?PackingCategoryConfigId");
				parameters.Add("?PackingCategoryConfigId", packingCategoryConfigId.Value, DbType.Int32);
            }
			if (!string.IsNullOrWhiteSpace(provinceCode))
			{
				wheres.Add("A.ProvinceCode = ?ProvinceCode");
				parameters.Add("?ProvinceCode", provinceCode, DbType.AnsiString);
			}
			if (!string.IsNullOrWhiteSpace(cityCode))
			{
				wheres.Add("A.CityCode = ?CityCode");
				parameters.Add("?CityCode", cityCode, DbType.AnsiString);
			}
			if (!string.IsNullOrWhiteSpace(countyCode))
			{
				wheres.Add("A.CountyCode = ?CountyCode");
				parameters.Add("?CountyCode", countyCode, DbType.AnsiString);
			}
			string sql = string.Format("{0} {1} ", QUERY, wheres.Count > 0 ? " WHERE " + string.Join(" AND ", wheres) : string.Empty);
			return await database.Slave.QueryAsync<PackingConfigViewEntity>(sql, parameters);
		}

        public async Task<PackingConfigOverwriteEntity> GetEntityByConfigId(int packingConfigId)
        {
			return await database.Slave.QueryEntityAsync<PackingConfigOverwriteEntity>($"{QUERY} WHERE A.PackingConfigId = ?PackingConfigId LIMIT 1", new { packingConfigId });
        }
    }
}
